﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Threading;
using MySql.Data.MySqlClient;
using eStock.Common;
using eStock.SQL;

namespace eStock.DataObjects
{
    public class ImageCollection : DataCollection<DBImage>
    {
        public override DBImage GetById(int id)
        {
            return List.Cast<DBImage>().FirstOrDefault(c => c.Id == id);
        }

        public override void Replace(DBImage newValue)
        {
            List[IndexOf(GetById(newValue.Id))] = newValue;
        }

        public bool Update(Dispatcher dispatcher)
        {
            bool state;
            try
            {
                
                SQLDataReader res;
                foreach (DBImage c in List)
                {
                    res = MainWindow.Database.QueryRead("SELECT * FROM es_images WHERE id = " + c.Id);
                    int i = MainWindow.Database.FetchCount(res);

                    if (i != 0)
                    {
                        Dictionary<string, object> parameters = new Dictionary<string, object>();
                        MainWindow.Database.QueryWrite(
                            "DELETE FROM es_images WHERE es_images.id = " + c.Id);
                        DBImage c1 = c;
                        dispatcher.Invoke(new Action(() =>
                                                              {
                                                                  parameters.Add("?id", c1.Id);
                                                                  parameters.Add("?title", MainWindow.Database.EscapeString(c1.Title));
                                                                  parameters.Add("?imgType", MainWindow.Database.EscapeString(c1.ImgType));
                                                                  parameters.Add("?image", c1.Image.ToByteArray());
                                                              }));
                        ((DBImage)List[List.IndexOf(c)]).Id =
                                Convert.ToInt32(
                                    MainWindow.Database.QueryWrite(
                                        "INSERT INTO es_images (id, title, imgType, image) VALUES (?id, ?title,?imgType,?image)",
                                        parameters));
                    }
                    else
                    {
                        Dictionary<string, object> parameters = new Dictionary<string, object>();
                        DBImage c1 = c;
                        dispatcher.Invoke(new Action(() =>
                        {
                            parameters.Add("?id", c1.Id);
                            parameters.Add("?title", MainWindow.Database.EscapeString(c1.Title));
                            parameters.Add("?imgType", MainWindow.Database.EscapeString(c1.ImgType));
                            parameters.Add("?image", c1.Image.ToByteArray());
                        }));
                        ((DBImage)List[List.IndexOf(c)]).Id =
                            Convert.ToInt32(
                                MainWindow.Database.QueryWrite(
                                    "INSERT INTO es_images (title, imgType, image) VALUES (?title,?imgType,?image)",
                                    parameters));
                    }
                }
                res = MainWindow.Database.QueryRead("SELECT * FROM es_images");
                Dictionary<string, object> data = MainWindow.Database.FetchArray(res);
                List<int> removeIds = new List<int>();
                while (data != null)
                {
                    DBImage c = GetById(Convert.ToInt32(data["id"]));
                    if (c == null)
                    {
                        removeIds.Add(Convert.ToInt32(data["id"]));
                    }
                    data = MainWindow.Database.FetchArray(res);
                }
                if (removeIds.Count > 0)
                {
                    string sqlString = "DELETE FROM es_images WHERE ";
                    for (int j = 0; j < removeIds.Count; j++)
                    {
                        if (j != 0)
                            sqlString += " AND ";
                        sqlString += "id = " + removeIds[j];
                    }
                    MainWindow.Database.QueryWrite(sqlString);
                }

                state = true;
            }
            catch (Exception)
            {
                state = false;
            }
            return state;
        }
        public override bool Update()
        {
            bool state;
           
                try
                {
                    foreach (DBImage c in List)
                    {
                        SQLDataReader res =
                            MainWindow.Database.QueryRead("SELECT * FROM es_images WHERE id = " + c.Id);
                        int i = MainWindow.Database.FetchCount(res);
                        if (i != 0)
                        {
                            MainWindow.Database.QueryWrite(
                                "DELETE FROM es_images WHERE es_images.id = " + c.Id);
                            Dictionary<string, object> parameters = new Dictionary
                                <string, object>
                                                                                                  {
                                                                                                      {"?id", c.Id},
                                                                                                      {
                                                                                                          "?title",
                                                                                                          MainWindow.Database.
                                                                                                          EscapeString(
                                                                                                              c.Title)
                                                                                                          },
                                                                                                      {
                                                                                                          "?imgType",
                                                                                                          MainWindow.Database.
                                                                                                          EscapeString(
                                                                                                              c.ImgType)
                                                                                                          },
                                                                                                      {
                                                                                                          "?image",
                                                                                                          c.Image.
                                                                                                          ToByteArray()
                                                                                                          }
                                                                                                  };
                            ((DBImage)List[List.IndexOf(c)]).Id =
                                Convert.ToInt32(
                                    MainWindow.Database.QueryWrite(
                                        "INSERT INTO es_images (id, title, imgType, image) VALUES (?id, ?title,?imgType,?image)",
                                        parameters));
                        }
                        else
                        {
                            Dictionary<string, object> parameters = new Dictionary
                                <string, object>
                                                                                                  {
                                                                                                      {
                                                                                                          "?title",
                                                                                                          MainWindow.Database.
                                                                                                          EscapeString(
                                                                                                              c.Title)
                                                                                                          },
                                                                                                      {
                                                                                                          "?imgType",
                                                                                                          MainWindow.Database.
                                                                                                          EscapeString(
                                                                                                              c.ImgType)
                                                                                                          },
                                                                                                      {
                                                                                                          "?image",
                                                                                                          c.Image.
                                                                                                          ToByteArray()
                                                                                                          }
                                                                                                  };
                            ((DBImage)List[List.IndexOf(c)]).Id =
                                Convert.ToInt32(
                                    MainWindow.Database.QueryWrite(
                                        "INSERT INTO es_images (title, imgType, image) VALUES (?title,?imgType,?image)",
                                        parameters));
                        }
                    }
                    state = true;
                }
                catch (Exception)
                {
                    state = false;
                }
            return state;
        }
    }
}
